// plugins/migrate.ts
import { readFile } from "node:fs/promises";
import { beginTransactional, db } from "~/lib/db";
import { debug } from "~/lib/log";

import s0 from "../../../resources/migrations/0000_initial.sql?asset";

const APP_MIGRATION_DIR: Array<{ version: number; file: string }> = [
  {
    version: 0,
    file: s0
  }
];

async function getLatestVersion() {
  const { rows } = await db.sql`SELECT COALESCE(MAX(version), -1) AS version FROM schema_version;`;
  if (rows) {
    const row = rows[0];
    if (row) {
      const { version } = row;
      if (typeof version !== "undefined" && version !== null) {
        return version as number;
      }
    }
  }
  return -1;
}

export async function runMigrations() {
  // 1. 检查 schema_version 表是否存在
  debug("1. 检查 schema_version 表是否存在");
  const { rows } = await db.sql` SELECT name
                  FROM sqlite_master
                  WHERE type = 'table'
                    AND name = 'schema_version';`;
  if (!rows || !rows.length) {
    debug("表不存在，创建 schema_version 表");
    await db.sql`
        CREATE TABLE schema_version
        (
            version    int PRIMARY KEY,
            applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
    `;
  } else {
    debug("表已存在，跳过创建");
  }

  // 2. 获取当前版本
  debug("2. 获取当前版本");
  const current = await getLatestVersion();
  debug("当前版本: " + current);

  for (const { file, version } of APP_MIGRATION_DIR) {
    if (version <= current) {
      debug("跳过文件：" + file + ",版本：" + version);
      continue;
    }
    const sql = await readFile(file, "utf-8");
    debug("开始处理文件：" + file + ",版本：" + version);
    try {
      await beginTransactional(async () => {
        debug("执行sql文件");
        await db.exec(sql);
        debug("插入版本");
        await db.sql`INSERT INTO schema_version(version)
                     VALUES (${version})`;
        console.info(`✅ migration ${file} applied`);
      });
    } catch (e) {
      console.error(`❌ migration ${file} failed`, e);
      throw e;
    }
  }
}
